package com.dfrz.bookstore.dao.impl;

import com.dfrz.bookstore.dao.Dao;
import com.dfrz.bookstore.entry.Idea;
import com.dfrz.bookstore.util.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 类功能描述:
 *         意见反馈Dao
 * @Author keyan
 * @Date 2021/5/22 13:29
 */
public class IdeaDaoImpl implements Dao<Idea> {

    @Override
    public boolean insert(Idea idea) {
        String sql = "insert into idea (title,content,user_id) values (?,?,?)";
        PreparedStatement prep = null;
        Connection conn = null;
        try {
            conn = JdbcUtils.getConnection();
            prep = conn.prepareStatement(sql);
            prep.setString(1,idea.getTitle());
            prep.setString(2,idea.getContent());
//            prep.setDate(3, (java.sql.Date) idea.getSend_time());
            prep.setInt(3,idea.getUserId());
            int i = prep.executeUpdate();
            if (i>0){
                return true;
            }

        } catch ( SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,prep,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public boolean deleteObjById(int i) {
        String sql = "delete from idea where id = "+i ;
        Connection conn = null;
        Statement stat;
        try {
            conn = JdbcUtils.getConnection();
            stat = conn.createStatement();
            int flag = stat.executeUpdate(sql);
            if (flag>0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateByObj(Idea idea) {
        if (idea.getId()==0){
            return false;
        }
        StringBuilder sb = new StringBuilder("update idea set id = "+idea.getId());
        if (idea.getTitle()!=null){
            sb.append(" , title = '"+idea.getTitle()+"' ");
        }
        if (idea.getContent()!=null){
            sb.append(" , content = '"+idea.getContent()+"' ");
        }
        if (idea.getSend_time()!=null){
            sb.append(" , send_time = '"+idea.getSend_time()+"' ");
        }
        if (idea.getUserId()!=0){
            sb.append(" , user_id = "+idea.getUserId());
        }

        sb.append(" where id = "+idea.getId());
        Connection conn = null;
        Statement stat = null;
        try {
            conn = JdbcUtils.getConnection();
            stat =  conn.createStatement();
            int i = stat.executeUpdate(sb.toString());
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public List<Idea> findObjByObj(Idea idea) {
        List<Idea> ideaList = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from idea where 1=1 ");
        if (idea.getId()!= 0){
            sb.append(" and id = "+idea.getId());
        }
        if (idea.getTitle()!=null){
            sb.append(" and type = '%"+idea.getTitle()+"%' ");
        }
        if (idea.getContent()!=null){
            sb.append(" and content = '%"+idea.getContent()+"%' ");
        }
        if (idea.getSend_time()!= null){
            sb.append(" and send_time = "+idea.getSend_time());
        }

        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                Idea idea1 = new Idea();
                idea1.setId(resultSet.getInt("id"));
                idea1.setTitle(resultSet.getString("title"));
                idea1.setContent(resultSet.getString("content"));
                idea1.setSend_time(resultSet.getDate("send_time"));
                idea1.setUserId(resultSet.getInt("user_id"));
                ideaList.add(idea1);
            }
            /*返回查找到的意见*/
            return ideaList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public List<Idea> findObjByPage(Idea idea, int pageNum, int pageSize) {
        int start = (pageNum-1)*pageSize;
        List<Idea> ideaList = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from idea where 1=1 ");
        if (idea.getId()!= 0){
            sb.append(" and id = "+idea.getId());
        }
        if (idea.getTitle()!=null){
            sb.append(" and type = '%"+idea.getTitle()+"%' ");
        }
        if (idea.getContent()!=null){
            sb.append(" and content = '%"+idea.getContent()+"%' ");
        }
        if (idea.getSend_time()!= null){
            sb.append(" and send_time = "+idea.getSend_time());
        }
        sb.append(" limit "+ start +","+pageSize);
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                Idea idea1 = new Idea();
                idea1.setId(resultSet.getInt("id"));
                idea1.setTitle(resultSet.getString("title"));
                idea1.setContent(resultSet.getString("content"));
                idea1.setSend_time(resultSet.getDate("send_time"));
                idea1.setUserId(resultSet.getInt("user_id"));
                ideaList.add(idea1);
            }
            /*返回查找到的分页的意见*/
            return ideaList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
